package com.ccjq.jq167.dao.impl;

import com.ccjq.jq167.dao.Sql_Dao;
import com.ccjq.jq167.po.StudentPo;
import com.ccjq.jq167.util.DbConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class Sql_Dao_Implement implements Sql_Dao {

    public List<StudentPo> getselect_students() {

        List<StudentPo> list = new ArrayList<StudentPo>();
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String select_students = "select * from students";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(select_students);
            res = pre.executeQuery();
            while (res.next()){
                StudentPo po = new StudentPo();
                po.setId(res.getString(1));
                po.setName(res.getString(2));
                po.setAge(res.getString(3));
                po.setSex(res.getString(4));
                po.setAddress(res.getString(5));
                po.setSchool(res.getString(6));
                list.add(po);
            }

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConnection.close(res, pre, con);
        }
        return list;

    }

    public int getinsert_students(StudentPo po) {
        int i = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String insert_students = "insert into students values(?,?,?,?,?,?)";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(insert_students);
            pre.setString(1,po.getId());
            pre.setString(2,po.getName());
            pre.setString(3,po.getAge());
            pre.setString(4,po.getSex());
            pre.setString(5,po.getAddress());
            pre.setString(6,po.getSchool());
            i = pre.executeUpdate();
            if(i>0){
                i=1;
            }else {
                return  i;
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConnection.close(res, pre, con);
        }
        return i;
    }

    public int getdelete_students(String id) {
        int i = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String delete_students = "delete from students wehre id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(delete_students);
            pre.setString(1,id);
            i = pre.executeUpdate();
            if(i>0){
                i=1;
            }else {
                return  i;
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConnection.close(res, pre, con);
        }
        return i;
    }

    public int getupdate_students(String name, String age, String sex, String address, String school, String id) {
        int i = 0;
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet res = null;
        String update_students = "update students set name=?,age=?,sex=?,address=?,school=? where id=?";
        try {
            con = DbConnection.getConnection();
            pre = con.prepareStatement(update_students);
            pre.setString(1,name);
            pre.setString(2,age);
            pre.setString(3,sex);
            pre.setString(4,address);
            pre.setString(5,school);
            pre.setString(6,id);
            i = pre.executeUpdate();
            if(i>0){
                i=1;
            }else {
                return  i;
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DbConnection.close(res, pre, con);
        }
        return i;
    }
}
